package com.jiepos.mpos.biz.channel.service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.InfChnhitchDao;
import com.jiepos.mpos.common.entity.App_Inf_ChnhitchEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.IDFactoryUtil;
import com.jiepos.mpos.core.util.StringUtils;


/**
 * 
 * 
 * <p>标题: —— 通道套扣信息表服务类</p>
 * <p>模块: 通用平台</p>
 * <p>版权: Copyright (c) 2017 江苏杰博实信息技术有限公司
 * @ProjectName:  [mpos] 
 * @Package com.jiepos.mpos.biz.channel.service 
 * @author 张学涛
 * @version 1.0
 * @since 2017年9月25日下午3:42:29
 * @history（历次修订内容、修订人、修订时间等）
 */
@Service
public class InfChnhitchService extends BaseService {
	
	@Autowired
	private InfChnhitchDao infChnhitchDao;
	
	
	/**
	 * 查询通道套扣信息列表json分页数据
	 * @param pagination
	 * @param infChnhitch
	 * @return
	 */
	public Map getChnhitchInfList(HttpServletRequest request,Pagination pagination,String ins_cd,String ins_cn_nm,
			String service_no,String service_name,String param_no,String param_name){
		StringBuffer sb = getInitSql("t1").append(",");
		sb.append("t1.chnhitch_uuid as uuid,NVL2 ( t2.ins_cd||t2.ins_cn_nm, t2.ins_cd||'-'||t2.ins_cn_nm, '') as insname,NVL2 ( t3.area_cd||t3.area_cn_nm, t3.area_cd||'-'||t3.area_cn_nm, '') as areaname,");
		sb.append("NVL2 ( t4.service_no||t4.service_name, t4.service_no||'-'||t4.service_name, '') as sername,NVL2 ( t5.param_no||t5.param_name, t5.param_no||'-'||t5.param_name, '') as purname,NVL2 ( t9.card_bin||t9.card_name||t9.card_ins_nm,t9.card_bin||'-'||t9.card_name||'-'||t9.card_ins_nm, '') as cardbin,NVL2 ( t12.super_bank_no||t12.bank_nm,t12.super_bank_no||'-'||t12.bank_nm, '') as cardins,");
		sb.append("NVL2 ( t10.code_val||t10.code_desc,t10.code_val||'-'||t10.code_desc, '') as crd_medium,NVL2 (t11.code_val||t11.code_desc,t11.code_val||'-'||t11.code_desc, '') as crd_nature,to_char(t1.min_amt,'fm9999999990.00') as min_amt,to_char(t1.max_amt,'fm9999999990.00') as max_amt,NVL2 (t6.channel_grp||t6.descpt,t6.channel_grp||'-'||t6.descpt, '') as chngrpname,");
		sb.append("NVL2 (t7.service_no||t7.service_name,t7.service_no||'-'||t7.service_name, '') as hsername,NVL2 (t8.param_no||t8.param_name,t8.param_no||'-'||t8.param_name, '') as hpurname from ");
		sb.append(Table.T_APP_INF_CHNHITCH);
		sb.append(" t1 left join ");
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t2 on t1.ins_uuid=t2.ins_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_AREA_CD);
		sb.append(" t3 on t1.area_uuid=t3.area_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_INF);
		sb.append(" t4 on t1.service_uuid=t4.service_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_PARAM);
		sb.append(" t5 on t1.service_param_uuid=t5.service_param_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_INF_CHANNEL_GRP);
		sb.append(" t6 on t1.channel_grp_uuid=t6.channel_grp_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_INF);
		sb.append(" t7 on t1.hitch_service_uuid=t7.service_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_PARAM);
		sb.append(" t8 on t1.hitch_service_param_uuid=t8.service_param_uuid ");		
		sb.append(" left join ");
		sb.append(Table.T_APP_CARD_BIN);
		sb.append(" t9 on t1.crd_bin=t9.card_bin  ");	
		sb.append(" left join ");
		sb.append(Table.T_APP_CODE_LIST);
		sb.append(" t10 on t1.crd_medium=t10.code_val and t10.code_tp='CRD_MEDIUM' ");
		sb.append(" left join ");
		sb.append(Table.T_APP_CODE_LIST);
		sb.append(" t11 on t1.crd_nature=t11.code_val and t11.code_tp='CRD_NATURE' ");
		sb.append(" left join ");
		sb.append(Table.T_APP_INF_SUPERBANK);
		sb.append(" t12 on t1.crd_ins=t12.super_bank_no  where 1=1");
		List list = new ArrayList();
		if (!StringUtils.isBlank(ins_cd)) {
			sb.append( " and t2.ins_cd like ? " );
			list.add("%" + ins_cd + "%");
		}
		if (!StringUtils.isBlank(ins_cn_nm)) {
			sb.append( " and t2.ins_cn_nm like ? " );
			list.add("%" + ins_cn_nm + "%");
		}
		if (!StringUtils.isBlank(service_no)) {
			sb.append( " and t4.service_no like ? " );
			list.add("%" + service_no + "%");
		}
		if (!StringUtils.isBlank(service_name)) {
			sb.append( " and t4.service_name like ? " );
			list.add("%" + service_name + "%");
		}
		if (!StringUtils.isBlank(param_no)) {
			sb.append( " and t5.param_no like ? " );
			list.add("%" + param_no + "%");
		}
		if (!StringUtils.isBlank(param_name)) {
			sb.append( " and t5.param_name like ? " );
			list.add("%" + param_name + "%");
		}
		pagination.queryString = sb.toString();
		pagination = super.getInsAuthSql(request, pagination, "t2", null);
		pagination = this.getOrderSql(pagination, "t1");
		return infChnhitchDao.getPaginationMap(pagination, "t1", list);				
	}
	
	/**
	 * 保存通道套扣信息
	 * @param infChnhitch
	 * @param map
	 */
	public void saveChnhitchInf(App_Inf_ChnhitchEntity infChnhitch,Map map){
		infChnhitch.setRec_upd_usr_id(String.valueOf(map.get(SystemConstant.SESSION_LOGIN_USER)));
		if (!StringUtils.isBlank(infChnhitch.getChnhitch_uuid())) {
			App_Inf_ChnhitchEntity newChnhitch=infChnhitchDao.findByParam("chnhitch_uuid", infChnhitch.getChnhitch_uuid());
			infChnhitch.setRec_upd_ts(new Timestamp(System.currentTimeMillis()));
			infChnhitch.setRec_id(newChnhitch.getRec_id());
			infChnhitch.setLast_oper_in(SystemConstant.OPER_UPDATE);
			List noChangeList = new ArrayList();
			noChangeList.add("chnhitch_uuid");
			noChangeList.add("rec_crt_ts");
			infChnhitchDao.update(infChnhitch,noChangeList);
		}else{
			infChnhitch.setRec_crt_ts(new Timestamp(System.currentTimeMillis()));
			infChnhitch.setRec_upd_ts(new Timestamp(System.currentTimeMillis()));
			infChnhitch.setChnhitch_uuid(IDFactoryUtil.getUUID());			
			infChnhitch.setRec_st(SystemConstant.NORMAL_STATUS);
			infChnhitch.setLast_oper_in(SystemConstant.OPER_ADD);
			infChnhitchDao.save(infChnhitch);
		}
		
	}
	
	/**
	 * 根据记录id查询实体对象
	 * @param chnhitch_uuid
	 * @return
	 */
	public Map getChnhitchInfById(String chnhitch_uuid){
		if (StringUtils.isBlank(chnhitch_uuid) || chnhitch_uuid.equals("undefined")) {
			return new HashMap();
		}
		StringBuilder sb = new StringBuilder();
		sb.append("select t1.chnhitch_uuid,t1.ins_uuid,NVL2 ( t2.ins_cd||t2.ins_cn_nm, t2.ins_cd||'-'||t2.ins_cn_nm, '') as insname,t1.area_uuid,NVL2 ( t3.area_cd||t3.area_cn_nm, t3.area_cd||'-'||t3.area_cn_nm, '') as areaname,t1.crd_ins,NVL2 ( t10.super_bank_no||t10.bank_nm,t10.super_bank_no||'-'||t10.bank_nm, '') as cardins,");
		sb.append("t1.service_uuid,NVL2 ( t4.service_no||t4.service_name, t4.service_no||'-'||t4.service_name, '') as sername,t1.service_param_uuid,NVL2 ( t5.param_no||t5.param_name, t5.param_no||'-'||t5.param_name, '') as purname,t1.crd_bin,NVL2 ( t9.card_bin||t9.card_name||t9.card_ins_nm,t9.card_bin||'-'||t9.card_name||'-'||t9.card_ins_nm, '') as cardbin,");
		sb.append("t1.crd_medium,t1.crd_nature,to_char(t1.min_amt,'fm9999999990.00') as min_amt,to_char(t1.max_amt,'fm9999999990.00') as max_amt,t1.channel_grp_uuid,NVL2 (t6.channel_grp||t6.descpt,t6.channel_grp||'-'||t6.descpt, '') as chngrpname,");
		sb.append("t1.hitch_service_uuid,NVL2 (t7.service_no||t7.service_name,t7.service_no||'-'||t7.service_name, '') as hsername,t1.hitch_service_param_uuid,NVL2 (t8.param_no||t8.param_name,t8.param_no||'-'||t8.param_name, '') as hpurname from ");
		sb.append(Table.T_APP_INF_CHNHITCH);
		sb.append(" t1 left join ");
		sb.append(Table.T_APP_INS_INF);
		sb.append(" t2 on t1.ins_uuid=t2.ins_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_AREA_CD);
		sb.append(" t3 on t1.area_uuid=t3.area_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_INF);
		sb.append(" t4 on t1.service_uuid=t4.service_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_PARAM);
		sb.append(" t5 on t1.service_param_uuid=t5.service_param_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_INF_CHANNEL_GRP);
		sb.append(" t6 on t1.channel_grp_uuid=t6.channel_grp_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_INF);
		sb.append(" t7 on t1.hitch_service_uuid=t7.service_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_SERVICE_PARAM);
		sb.append(" t8 on t1.hitch_service_param_uuid=t8.service_param_uuid ");
		sb.append(" left join ");
		sb.append(Table.T_APP_INF_SUPERBANK);
		sb.append(" t10 on t1.crd_ins=t10.super_bank_no ");
		sb.append(" left join ");
		sb.append(Table.T_APP_CARD_BIN);
		sb.append(" t9 on t1.crd_bin=t9.card_bin where t1.rec_st!='");		
		sb.append(SystemConstant.DEL_STATUS);
		sb.append("' and t1.chnhitch_uuid='");
		sb.append(chnhitch_uuid+"'");
		return infChnhitchDao.queryForMap(sb.toString());
	}

	/**
	 * 删除通道套扣信息
	 * @param chnhitch_uuids
	 */
	public void deleteChnhitchInfByIds(String chnhitch_uuids){
		infChnhitchDao.deleteByStatus("chnhitch_uuid", chnhitch_uuids);
	}
}
